package com.example.demo.excel.util;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @desc:数据导出，生成excel文件
 * @author: chao
 * @time: 2018.6.12
 */
@Service
public class ExportExcelUtil {
    /**
     * excel导出，有码值的数据使用下拉框展示。
     *
     * @param col      列名
     * @param boxMap   码值集合
     * @param firstRow 插入下拉框开始行号
     * @param lastRow  插入下拉框结束行号
     * @param firstCol 插入下拉框开始列号
     * @param lastCol  插入下拉框结束列号
     * @return
     */
    public static HSSFDataValidation createBox(String col, Map<String, String> boxMap, int firstRow, int lastRow,
                                               int firstCol, int lastCol) {
        HSSFDataValidation dataValidation = null;
        // 查询码值表
        String cols = "";
        if (null != boxMap.get(col)) {
            cols = boxMap.get(col);
        }
        // 设置下拉框
        if (cols.length() > 0 && null != cols) {
            String str[] = cols.split(",");
            // 指定0-9行，0-0列为下拉框
            CellRangeAddressList cas = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            // 创建下拉数据列
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str);
            // 将下拉数据放入下拉框
            dataValidation = new HSSFDataValidation(cas, dvConstraint);
        }
        return dataValidation;
    }

    /**
     * @param @param fileName Excel文件名称
     * @param @param downData 下拉框数据(数组)
     * @param @param downRows 下拉列的序号(数组,序号从0开始)
     * @param @param List<Map<String, String>> src_list 默认塞入数据
     * @param @param titleMap 标题的map
     * @param @param titleKeyList 标题的对应字段
     * @return void @throws
     * @throws IOException
     * @Title: createExcelTemplate
     * @Description: 导出数据带有下拉框--例子是滨江班主任学生家长导出
     */
    public void expoerDataExcel1(HttpServletResponse response, ArrayList<String> titleKeyList,
                                Map<String, String> titleMap, List<Map<String, Object>> src_list, String fileName, List<String[]> downData,
                                List<Integer> downRows) throws IOException {

        // Excel2003以前（包括2003）的版本，扩展名是.xls
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        Row nRow = null; // 行对象
        Cell nCell = null; // 列对象

        int rowNo = 0; // 总行号
        int pageRowNo = 0; // 页行号
        for (int k = 0; k < src_list.size(); k++) {
            Map<String, Object> srcMap = src_list.get(k);
            //写入200000条后切换到下个工作表
            if (rowNo % 20000 == 0) {
                pageRowNo = 0; // 新建了工作表,重置工作表的行号为0
                // -----------定义表头-----------
                nRow = sheet.createRow(pageRowNo++);

                //第一行---标题栏
                for (int i = 0; i < titleKeyList.size(); i++) {
                    Cell cell_tem = nRow.createCell(i);
                    cell_tem.setCellValue(titleMap.get(titleKeyList.get(i)));
                }
                rowNo++;
            }
            rowNo++;
            nRow = sheet.createRow(pageRowNo++); // 新建行对象

            // 行，获取cell值
            for (int j = 0; j < titleKeyList.size(); j++) {
                nCell = nRow.createCell(j);
                if (srcMap.get(titleKeyList.get(j)) != null) {
                    nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString());
                } else {
                    nCell.setCellValue("");
                }
            }
        }

        for (int i = 0; i < downRows.size(); i++) {
            String col = downRows.get(i).toString(); // 下拉列数组序号
            Map<String, String> boxMap = new HashMap<>();

            String[] strings = downData.get(i);
            String s = "";
            for (String string : strings) {
                s += string + ",";
            }
            boxMap.put(downRows.get(i).toString(), s);
            HSSFDataValidation dataValidation = createBox(col, boxMap, 1, src_list.size(), downRows.get(i),
                    downRows.get(i));
            if (dataValidation != null) {
                sheet.addValidationData(dataValidation);
            }
        }

        String fileNameURL = java.net.URLEncoder.encode(fileName + ".xls", "UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("content-disposition",
                "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.flushBuffer();
        response.setCharacterEncoding("UTF-8");
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }

    public void expoerDataExcel(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String, Object>> src_list, String fileName) throws IOException {

        //内存中只创建100个对象
        Workbook wb = new SXSSFWorkbook(100);           //关键语句
        Sheet sheet = null;     //工作表对象
        Row nRow = null;        //行对象
        Cell nCell = null;      //列对象

        int rowNo = 0;      //总行号
        int pageRowNo = 0;  //页行号

        for (int k = 0; k < src_list.size(); k++) {
            Map<String, Object> srcMap = src_list.get(k);
            //写入200000条后切换到下个工作表
            if (rowNo % 20000 == 0) {
                System.out.println("Current Sheet:" + rowNo / 20000);
                sheet = wb.createSheet("Sheet" + (rowNo / 20000));//建立新的sheet对象
                sheet = wb.getSheetAt(rowNo / 200000);        //动态指定当前的工作表
                pageRowNo = 0;      //新建了工作表,重置工作表的行号为0
                // -----------定义表头-----------
                nRow = sheet.createRow(pageRowNo++);
                // 列数 titleKeyList.size()
                for (int i = 0; i < titleKeyList.size(); i++) {
                    Cell cell_tem = nRow.createCell(i);
                    cell_tem.setCellValue(titleMap.get(titleKeyList.get(i)));
                }
                rowNo++;
                // ---------------------------
            }
            rowNo++;
            nRow = sheet.createRow(pageRowNo++);    //新建行对象

            // 行，获取cell值
            for (int j = 0; j < titleKeyList.size(); j++) {
                nCell = nRow.createCell(j);
                if (srcMap.get(titleKeyList.get(j)) != null) {
                    nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString());
                } else {
                    nCell.setCellValue("");
                }
            }
        }
        String fileNameURL = java.net.URLEncoder.encode(fileName + ".xlsx", "UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.flushBuffer();
        response.setCharacterEncoding("UTF-8");
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }

    /*表头直接从查询结果中得到，不需要手动写映射*/
    @SuppressWarnings("unchecked")
    public void expoerDataExcel(HttpServletResponse response, List<Object[]> list, String fileName) throws IOException {
        //内存中只创建100个对象
        Workbook wb = new SXSSFWorkbook(100);//关键语句
        Sheet sheet = null;     //工作表对象
        Row nRow = null;        //行对象
        Cell nCell = null;      //列对象
        int pageRowNo = 0;  //页行号

//        font2.setFontHeightInPoints((short) 12);

        for (Object[] obj : list) {
            SqlRowSetMetaData metadata = (SqlRowSetMetaData) obj[0];//数据查询字段名
            List<String[]> rowdata = (List<String[]>) obj[1];//行数据
            String sheetname = (String) obj[2];//sheet名称
            sheet = wb.createSheet(sheetname);
            pageRowNo = 0;
            // -----------定义表头-----------
            CellStyle style3 = wb.createCellStyle();
            style3.setAlignment(HorizontalAlignment.CENTER);
            style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style3.setBorderBottom(BorderStyle.THIN);
            style3.setBorderLeft(BorderStyle.THIN);
            style3.setBorderRight(BorderStyle.THIN);
            style3.setBorderTop(BorderStyle.THIN);
            Font font2 = wb.createFont();
            font2.setBold(true);//粗体显示     	
            style3.setFont(font2);//选择需要用到的字体格式
//        	style3.setFillForegroundColor(HSSFColorPredefined.LIGHT_GREEN.getIndex());
            nRow = sheet.createRow(pageRowNo++);
            for (int i = 0; i < metadata.getColumnCount(); i++) {
                Cell cell_tem = nRow.createCell(i);
                cell_tem.setCellValue(metadata.getColumnName(i + 1));
                cell_tem.setCellStyle(style3);
            }

            for (String[] arr : rowdata) {
                nRow = sheet.createRow(pageRowNo++);    //新建行对象
                for (int i = 0; i < arr.length; i++) {
                    nCell = nRow.createCell(i);
                    nCell.setCellValue(arr[i]);
                }
            }
        }

        String fileNameURL = java.net.URLEncoder.encode(fileName + ".xlsx", "UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
        response.flushBuffer();
        response.setCharacterEncoding("UTF-8");
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }

    /*导出翼校通活跃度统计数据，根据固定的模板头导出Excel文件*/
    @SuppressWarnings("unchecked")
    public void expoerActiveDataExcel(HttpServletResponse response, List<Object[]> list, String fileName) throws IOException {
        try {
            //读取固定模板中的表头
            ClassPathResource cpr = new ClassPathResource("/static/appactivedata.xlsx");
            InputStream is = cpr.getInputStream();
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            Sheet sheet0 = workbook.getSheetAt(0);
            Row row1 = sheet0.getRow(0);
            int cellNum = row1.getLastCellNum();//得到表头列的个数

            Object[] obj = list.get(0);
            SqlRowSetMetaData metadata = (SqlRowSetMetaData) obj[0];//数据查询字段名
            List<String[]> rowdata = (List<String[]>) obj[1];//行数据
            String sheetname = (String) obj[2];//sheet名称
            workbook.setSheetName(0, sheetname);

//            long start = System.currentTimeMillis();
            for (int j = 0; j < rowdata.size(); j++) {
                Row row = sheet0.createRow(j + 1);//从第二行开始填充数据
                for (int i = 0; i < cellNum; i++) {
                    Cell celli = row1.getCell(i);
                    String cellValuei = celli.getStringCellValue();
                    for (int ii = 0; ii < cellNum; ii++) {
                        String columnName = metadata.getColumnName(ii + 1);
                        if (cellValuei.equals(columnName)) {
                            row.createCell(i).setCellValue(rowdata.get(j)[ii]);
                        }
                    }
                }
            }
//            long end = System.currentTimeMillis();
//            System.out.println("执行时间=="+(end-start));
            String fileNameURL = java.net.URLEncoder.encode(fileName + ".xlsx", "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("content-disposition", "attachment;filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL);
            response.flushBuffer();
            response.setCharacterEncoding("UTF-8");
            OutputStream outputStream = response.getOutputStream();
            workbook.write(response.getOutputStream());
            workbook.close();
            outputStream.flush();
            outputStream.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}